package com.cn.yang.dao.impl;

import com.cn.yang.dao.NewsDao;
import com.cn.yang.model.news.NewsClassifyModel;
import com.cn.yang.model.news.NewsModel;
import com.cn.yang.util.JDBC;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class NewsDaoImpl implements NewsDao {
    JDBC jdbc = new JDBC();
    //查询 新闻种类
    @Override
    public List<NewsClassifyModel> findClassify() {
        String sql = "select * from newsclassify";
        ResultSet resultSet = jdbc.getResultSet(sql);
        List<NewsClassifyModel> list = new ArrayList<>();
        try {
            while (resultSet.next()) {
                NewsClassifyModel model = new NewsClassifyModel();
                model.setId(resultSet.getInt("id"));
                model.setClassify(resultSet.getString("classify"));
                list.add(model);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        jdbc.closeConn();
        return list;
    }
    //添加新闻
    @Override
    public int addNews(NewsModel model) {
        String sql = "insert into news(title,content,time,type) values ('"+model.getTitle()+"','"+model.getContent()+"','"+model.getTime()+"','"+model.getType()+"')";
        int result = jdbc.update(sql);
        jdbc.closeConn();
        return result;
    }
    //查询新闻
    @Override
    public List<NewsModel> findNews(NewsModel newsModel) {
        String sql = "select a.*,b.classify as classifyname from news a LEFT JOIN newsclassify b ON a.type=b.id limit "+newsModel.getPageUtil().getPageNumber()+","+newsModel.getPageUtil().getPageSize()+"";
        ResultSet resultSet = jdbc.getResultSet(sql);
        List<NewsModel> list = new ArrayList<>();
        try {
            while (resultSet.next()) {
                NewsModel model = new NewsModel();
                model.setId(resultSet.getInt("id"));
                model.setTitle(resultSet.getString("title"));
                model.setContent(resultSet.getString("content"));
                model.setTime(resultSet.getString("time"));
                model.setType(resultSet.getInt("type"));
                model.setClassifyName(resultSet.getString("classifyname"));
                list.add(model);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        jdbc.closeConn();
        return list;
    }
    @Override
    public List<NewsModel> findNews() {
        String sql = "select a.*,b.classify as classifyname from news a LEFT JOIN newsclassify b ON a.type=b.id";
        ResultSet resultSet = jdbc.getResultSet(sql);
        List<NewsModel> list = new ArrayList<>();
        try {
            while (resultSet.next()) {
                NewsModel model = new NewsModel();
                model.setId(resultSet.getInt("id"));
                model.setTitle(resultSet.getString("title"));
                model.setContent(resultSet.getString("content"));
                model.setTime(resultSet.getString("time"));
                model.setType(resultSet.getInt("type"));
                model.setClassifyName(resultSet.getString("classifyname"));
                list.add(model);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        jdbc.closeConn();
        return list;
    }
    //查询总条数
    @Override
    public int countNews() {
        String sql = "select count(*) as count from news a LEFT JOIN newsclassify b ON a.type=b.id";
        ResultSet result = jdbc.getResultSet(sql);
        int count = 0;
        try{
            while (result.next()){
                count = result.getInt("count");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }
    //查询一条 id ======
    @Override
    public NewsModel selectById(Integer id) {
        String sql = "select a.*,b.classify as classifyname from news a LEFT JOIN newsclassify b ON a.type=b.id where a.id = "+id;
        ResultSet resultSet = jdbc.getResultSet(sql);
        NewsModel model = new NewsModel();
        try {
            while (resultSet.next()) {
                model.setId(resultSet.getInt("id"));
                model.setTitle(resultSet.getString("title"));
                model.setContent(resultSet.getString("content"));
                model.setTime(resultSet.getString("time"));
                model.setType(resultSet.getInt("type"));
                model.setClassifyName(resultSet.getString("classifyname"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        jdbc.closeConn();
        return model;
    }
//更新数据
    @Override
    public int updateNews(NewsModel model) {
        String sql = "update news set title = '"+model.getTitle()+"',content ='"+model.getContent()+"',time = '"+model.getTime()+"',type= '"+model.getType()+"' where id = "+model.getId()+"";
        int result = jdbc.update(sql);
        jdbc.closeConn();
        return result;
    }
//删除数据---news
    @Override
    public int deleteById(Integer id) {
        String sql = "delete from news where id = "+id;
        int result = jdbc.update(sql);
        jdbc.closeConn();
        return result;
    }
}
